create or replace procedure USP_SYNC_APPLICATION
AS
BEGIN
   insert into t_sync_application(app_code)
   select app_code from tb_application_temp;

   update tb_application a
   set (app_code,app_name,app_type,comments,update_date)
   =
   (select app_code,app_name,app_type,comments,sysdate
    from tb_application_temp b
    where b.app_code=a.app_code
    )
    where exists( select * from t_sync_application c where c.app_code=a.app_code)
    ;

   insert into tb_application
     (app_code, app_name, app_type, comments,update_date,sync_date)
   select
     app_code, app_name, app_type, comments,sysdate,sysdate
   from tb_application_temp a
   where not exists( select * from tb_application b where b.app_code=a.app_code)
   --and exists( select * from t_sync_application t where t.app_code=a.app_code)
   ;

   delete from tb_application_temp a
   where exists(select * from t_sync_application t where t.app_code=a.app_code)
   ;

   Commit;
    Exception
      When Others Then
      Rollback;
      usp_insert_synclog( P_LINE_NUMBER => SQLERRM, P_ERR_CODE => SQLCODE, P_ERR_MESSAGE => 'USP_SYNC_EVENT error' );



END;
/
